package com.eascs.afterloan.dao;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.afterloan.entity.DeclareSignEntity;
import com.eascs.afterloan.form.DeclareSignForm;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;

@Repository
public class DeclareSignDao extends BaseO2oDao {

	/**
	 * 分页查询
	 * 
	 * @param form
	 * @return
	 */
	public PageResult<Object[]> queryByPage(DeclareSignForm form) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, form.getPageSize(), form.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT ol.bill_code,olu.user_full_name,opr.province_name,ol.ltd_name,ob.loan_bank");
		querySql.append(" ,ob.loan_date,ob.loan_amount/10000,case when ol.loan_flg='Y' then '已放款' else '未放款' end,ob.oprate_time,ob.oprate_psn");
		querySql.append(" FROM o2o_loanapply ol left join o2o_bank_loan_data ob on ol.bill_code=ob.bill_code");
		querySql.append(" left join o2o_loan_user olu on ol.loan_id=olu.loan_id");
		querySql.append(" LEFT JOIN o2o_platform_regional opr on ol.ltd = opr.platform_id");
		querySql.append(" where /**DATA_PERMISSION(ol.LTD)**/ ol.declare_sign='Y'");
		if (StringUtil.isNotBlank(form.getBillCode())) {
			querySql.append(" and ol.bill_code like ?");
			sqlPageQuery.addLikeParam(form.getBillCode());
		}
		if (StringUtil.isNotBlank(form.getLoanUserName())) {
			querySql.append(" and olu.user_full_name like ?");
			sqlPageQuery.addLikeParam(form.getLoanUserName());
		}
		if (StringUtil.isNotBlank(form.getLoanBankName())) {
			querySql.append(" and ob.loan_bank = ? ");
			sqlPageQuery.addParam(form.getLoanBankName());
		}
		if (StringUtil.isNotBlank(form.getProvinceName())) {
			querySql.append(" and opr.province_code = ? ");
			sqlPageQuery.addParam(form.getProvinceName());
		}
		if (StringUtil.isNotBlank(form.getLoanStarTime())) {
			querySql.append(" and ob.loan_date >= ?");
			sqlPageQuery.addParam(form.getLoanStarTime());
		}
		if (StringUtil.isNotBlank(form.getLoanEndTime())) {
			querySql.append(" and ob.loan_date < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(form.getLoanEndTime()), 1)));
		}
		querySql.append(" GROUP BY ol.bill_code ORDER BY ol.end_date DESC");
		sqlPageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}

	/**
	 * 根据身份证号和姓名查询可更新放款条数
	 * 
	 * @param userName
	 * @param cardNo
	 * @return
	 */
	public int queryByCount(String userName, String cardNo) {
		String sql = " SELECT count(ol.bill_code) FROM o2o_loanapply ol left join o2o_loan_user olu on ol.loan_id=olu.loan_id and ol.loanapply_no = olu.loanapply_no where ol.declare_sign='Y' and olu.user_full_name = ? and  olu.document_no = ? GROUP BY ol.bill_code";
		Integer i = this.findInteger(sql, userName, cardNo);
		if (null == i)
			return 0;
		else
			return i;
	}

	/**
	 * 根据身份证号和姓名查询贷款单号ID
	 * 
	 * @param userName
	 * @param cardNo
	 * @return
	 */
	public String queryByLoanAppLyNo(String userName, String cardNo) {
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT ol.loanapply_no ");
		querySql.append(" FROM o2o_loanapply ol left join o2o_bank_loan_data ob on ol.bill_code=ob.bill_code");
		querySql.append(" left join o2o_loan_user olu on ol.loan_id=olu.loan_id");
		querySql.append(" LEFT JOIN o2o_platform_regional opr on ol.ltd = opr.platform_id");
		querySql.append(" where ol.declare_sign='Y' and olu.user_full_name = ? and  olu.document_no = ? GROUP BY ol.bill_code");
		return this.findString(querySql.toString(), userName, cardNo);
	}

	/**
	 * 根据身份证号和姓名查询是否存在已放款的单
	 * 
	 * @param userName
	 * @param cardNo
	 * @return
	 */
	public String queryByLoanFlg(String userName, String cardNo) {
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT ol.loanapply_no ");
		querySql.append(" FROM o2o_loanapply ol left join o2o_bank_loan_data ob on ol.bill_code=ob.bill_code");
		querySql.append(" left join o2o_loan_user olu on ol.loan_id=olu.loan_id");
		querySql.append(" LEFT JOIN o2o_platform_regional opr on ol.ltd = opr.platform_id");
		querySql.append(" where ol.declare_sign='Y' and ol.loan_flg='Y' and olu.user_full_name = ? and olu.document_no = ? GROUP BY ol.bill_code");
		return this.findString(querySql.toString(), userName, cardNo);
	}

	public PageResult<Object[]> queryByPageDeclareSign() {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<Object[]>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer querySql = new StringBuffer();
		querySql.append("select bill_code,customer_name,province_name,platform,bank_loan_name,loan_date,amount/10000 from o2o_bank_loan_temporary order by udpate_date ASC");
		sqlPageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}

	public List<DeclareSignEntity> queryList() {
		String sql = "select * from o2o_bank_loan_temporary";
		return this.findEntitys(sql, DeclareSignEntity.class);
	}

	public int delete() {
		String sql = "DELETE FROM o2o_bank_loan_temporary";
		return this.execute(sql);
	}
	
	public String queryByBankName(String bankName){
		String sql = "SELECT pd.item_code from pub_dditem pd where pd.dd_no='10340' and pd.item_name = ?";
		return this.findString(sql, bankName);
	}
}
